package cn.nwpu.eckel.Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.nwpu.eckel.model.Forecast;
import cn.nwpu.eckel.model.Order;
import static cn.nwpu.eckel.utility.DBConnection.*;

public class OrderDao {
	public static List<Order> getOrdersByMpsNo(int mpsNo){
		List<Order> orders=new ArrayList<Order>();
		Order tempOrder;
		Connection con=getConnection();
		ResultSet rs=null;
		PreparedStatement pstmt=null;
		String sql="select * from order_tb where mpsNo=?";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1,mpsNo);
			rs=pstmt.executeQuery();
			while(rs.next()){
				tempOrder=new Order(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4),rs.getString(5),rs.getDate(6));
				orders.add(tempOrder);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
        return orders;
	}
	public static Order roll(int mpsNo){
		Order o=new Order();
		Connection con=getConnection();
		PreparedStatement pstmt_1=null;
		PreparedStatement pstmt_2=null;
		PreparedStatement pstmt_3=null;
		ResultSet rs=null;
		String sql_1="select * from order_tb where mpsNo=? and ts=0";
		String sql_2="delete from order_tb where mpsNo=? and ts=0";
		String sql_3="update order_tb set ts=ts-1 where mpsNo=?";
		try{
			pstmt_1=con.prepareStatement(sql_1);
			pstmt_2=con.prepareStatement(sql_2);
			pstmt_3=con.prepareStatement(sql_3);
			
			pstmt_1.setInt(1,mpsNo);
			rs=pstmt_1.executeQuery();
			
			pstmt_2.setInt(1,mpsNo);
			pstmt_2.executeUpdate();
			
			pstmt_3.setInt(1,mpsNo);
			pstmt_3.executeUpdate();
			
			while(rs.next()){
				o.id=rs.getInt(1);
				o.mpsNo=rs.getInt(2);
				o.ts=rs.getInt(3);
				o.quantity=rs.getInt(4);
				o.people=rs.getString(5);
				o.buildTime=rs.getDate(6);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt_1);
			close(pstmt_2);
			close(con);
		}
		return o;
	}
	public static Order deleteOrder(int id){
		Order order=OrderDao.getOrderById(id);
		Connection con=getConnection();
		PreparedStatement pstmt=null;
		String sql="delete from Order_tb where id=?";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1,id);
			pstmt.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
		return order;
	}
	public static Order getOrderById(int id){
		Order order=new Order();
		Connection con=getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		String sql="select* from Order_tb where id=?";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1,id);
			rs=pstmt.executeQuery();
			while(rs.next()){
				order.id=rs.getInt(1);
				order.mpsNo =rs.getInt(2);
				order.ts=rs.getInt(3);
				order.quantity=rs.getInt(4);
				order.people=rs.getString(5);
				order.buildTime=rs.getDate(6);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
		return order;
	}
	
    public static void insertOrder(Order order){
    	Connection con=getConnection();
		PreparedStatement pstmt=null;
		String sql="insert into Order_tb values(?,?,?,?,?,?)";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, order.id);
			pstmt.setInt(2, order.mpsNo);
			pstmt.setInt(3, order.ts);
			pstmt.setInt(4, order.quantity);
			pstmt.setString(5, order.people);
			pstmt.setDate(6, order.buildTime);
			pstmt.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
	}
	public static Order alterOrder(Order newOdr){
		Order oldOdr=OrderDao.getOrderById(newOdr.id);
		Connection con=getConnection();
		PreparedStatement pstmt=null;
		String sql="update order_tb set quantity=?,people=?,buildTime=? where mpsno=? and ts=?";
		System.out.println(sql);
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, newOdr.quantity);
			pstmt.setString(2,newOdr.people);
			pstmt.setDate(3,newOdr.buildTime);
			pstmt.setInt(4, newOdr.mpsNo);
			pstmt.setInt(5, newOdr.ts);
			pstmt.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
		return oldOdr;
	}

}
